package 人事管理系统.Connect;

import 人事管理系统.Tables.Changeemp;
import 人事管理系统.Tables.Employee;
import 人事管理系统.Tables.Opdep;

import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.*;
import java.util.ArrayList;

public class Change_Database {
    private  String ip=this.getIp();
    private String url="jdbc:mysql://"+ip+":3306/人事管理系统?useOldAliasMetadataBehavior=true";
    //获取ip
    public String getIp(){
        InetAddress addr = null;
        try {
            addr = InetAddress.getLocalHost();
        } catch (UnknownHostException e) {
            e.printStackTrace();
        }
        System.out.println("Local HostAddress: "+addr.getHostAddress());
        String hostname = addr.getHostName();
        System.out.println("Local host name: "+hostname);
        return addr.getHostAddress();
    }
    /**********************************************************************************************************************/

    /*
   查询员工调动记录
    */
    public ArrayList<Changeemp> findAll()   {
        ArrayList<Changeemp> list=new ArrayList<>();
        Connection con=null;
        Statement stat=null;
        ResultSet rs =null;
        try {
            //1.导入jar包
            //2.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3.获取连接
            con= DriverManager.getConnection(url,"root","020214YxY");
            //4.获取执行者对象
            stat=con.createStatement();
            //5.执行sql语句，并接收结果
            String sql="SELECT * FROM changeemp ";
            rs = stat.executeQuery(sql);
            //6.处理结果

            while(rs.next()){
                //封装对象
                Changeemp op=new Changeemp();
                op.setTransferno(rs.getString("TransferNO"));
                op.setEno(rs.getString("ENO"));
                op.setNowdep(rs.getString("NowDEP"));
                op.setReason(rs.getString("Reason"));
                list.add(op);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //7.释放资源
            if(con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(rs!=null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stat!=null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }

        return list;
    }
    /**********************************************************************************************************************/
   /*
    插入一条员工调动信息
     */
    public  int insert(Changeemp cemp){

        Connection con=null;
        Statement stat=null;
        int result=0;//修改的行数
        try {
            //1.导入jar包
            //2.注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //3.获取连接
            con= DriverManager.getConnection(url,"root","020214YxY");
            //4.获取执行者对象
            stat=con.createStatement();
            //5.执行sql语句，并接收结果
            // SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
            String sql;
            if(cemp.getNowdep().equals("")) {
                 sql = "INSERT INTO changeemp(TransferNO,ENO,Reason) VALUES ('" + cemp.getTransferno() + "','" + cemp.getEno() + "','" + cemp.getReason() + "')";
            }else
            {
                 sql = "INSERT INTO changeemp VALUES ('" + cemp.getTransferno() + "','" + cemp.getEno() + "','" + cemp.getNowdep() + "','" + cemp.getReason() + "')";
            }

            result=stat.executeUpdate(sql);
            //6.处理结果

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //成功弹窗
            //new opyes();
            //7.释放资源
            if(con!=null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(stat!=null) {
                try {
                    stat.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

        }
        return result;
    }
}
